﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using FWPDocumentation.Common.DbTools;

namespace FWPDocumentation.Entities
{
    public class Faculty
    {
        public const String TABLE_NAME = "`fvp`.`faculty`";
        public const String ID = "`idFaculty`";
        public const String ID_NAME = "`Faculty`";
        public const String PARENT_ID = "`Institute_idInstitute`";
        public int idFaculty { get; set; }
        public String nameFaculty { get; set; }
        public int idInstitute { get; set; }

        public Faculty(){}

        private Faculty(int id, String name , int parentId)
        {
            this.idInstitute = parentId;
            this.nameFaculty = name;
            this.idFaculty = id;
        }

        public static Faculty CreateFaculty(String name, int idInst)
        {
            var insertCommand = new SqlCommand("SELECT " + ID + " FROM " + TABLE_NAME + "WHERE " + ID_NAME + "='" + name+"' AND "+PARENT_ID+"='"+idInst+"'");
            int returnedID = insertCommand.ExecuteScalar<int>();
            if (returnedID != 0)
            {
                return null;
            }
            insertCommand = new SqlCommand(
                "INSERT INTO " + TABLE_NAME + "(" + ID_NAME+","+PARENT_ID + ")VALUES('" + name+"','"+idInst + "');");
            insertCommand.ExecuteNonQuery();
            insertCommand = new SqlCommand("SELECT " + ID + " FROM " + TABLE_NAME + "WHERE " + ID_NAME + "='" + name + "' AND " + PARENT_ID + "='" + idInst + "'");
            returnedID = insertCommand.ExecuteScalar<int>();
            return new Faculty(returnedID,name,idInst);
        }

        

        public override String ToString()
        {
            return nameFaculty;
        }

        public Institute getInstitut()
        {
            var insertCommand = new SqlCommand("SELECT idInstitute, Institute as name FROM " + Institute.TABLE_NAME+ " WHERE "+Institute.ID+"="+idInstitute);
            return insertCommand.ExecuteScalar<Institute>();
        }

        public List<Group> getGroups()
        {
            var insertCommand = new SqlCommand("SELECT " + Group.ID + "," + Group.ID_NAME + " as nameGroup," + Group.PARENT_ID + " as idFaculty FROM " + Group.TABLE_NAME + "WHERE " + Group.PARENT_ID + "=" + idFaculty);
            return insertCommand.ExecuteQuery<Group>();
        }



    }
}
